<?php
namespace App\Repositories;

use App\Exceptions\BasicException;
use App\Services\Tools\CommonService;
use Illuminate\Support\Facades\DB;  //引用DB
use App\Services\Tools\FileService;   //引入文件处理类

class SociatyRepository
{

    /**
     * 根据公会号查询公会详情(公会和厅共用)
     * @param $no
     * @return mixed
     */
    public function sociatyDetailByNo($no,$type=1)
    {
        $sociaty = app('model_sc_sociaty')->where('sociaty_no',$no)->where('type',$type)->first();

        return $sociaty;
    }

    //通过user_code获取user_id
    public function getUserInfoByCode($user_code)
    {
        $user = app('model_user_data')->where('user_code',$user_code)->first();

        return $user;
    }

    /**
     * 根据uid查询公会详情(公会和厅共用)
     * @param $uid
     * @return mixed
     */
    public function sociatyDetailByUid($uid,$type=1)
    {
        $sociaty = app('model_sc_sociaty')->where('user_id',$uid)->where('type',$type)->first();

        return $sociaty;
    }

    /**
     * 根绝用户编号查询房间详情
     * @param $uid
     * @return mixed
     */
    public function roomDetailByUid($uid)
    {
        $room = app('model_user_room')->where('user_id',$uid)->first();

        return $room;
    }

    /**
     * 添加公会
     * @param $param
     * @return bool
     * @throws BasicException
     */
    public function sociatyAdd($param)
    {
        $res = app('model_sc_sociaty')->create($param);
        if($res) return $res;   //事务获取ID需要
        throw new BasicException(90005);
    }

    /**
     * 添加管理员
     * @param $param
     * @return bool
     * @throws BasicException
     */
    public function adminAdd($param)
    {
        $admin = app('model_sc_admin')->create($param);
        if($admin) return $admin;
        throw new BasicException(90007);
    }


    /**
     * 根据type获得所有menuid的字串
     * @param $type
     * @return string
     */
    public function getMenusStrByType($type)
    {
        $menus = app('model_sc_menu')->where('type',$type)->select('menu_id')->get();
        $str = '';
        foreach($menus as &$val)
        {
            $str .= $val->menu_id.',';
        }
        //去掉最后一位
        $str = substr($str, 0, -1);

        return $str;
    }


    /**
     * 根据菜单字串返回所有权限id字串
     * @param $menuStr
     * @return string
     */
    public function getPermissionStrByMenuId($menuStr)
    {
        $permissions = app('model_sc_permission')->whereIn('menu_id',explode(',',$menuStr))->select('permission_id')->get();
        $str = '';
        foreach($permissions as &$val)
        {
            $str .= $val->permission_id.',';
        }
        //去掉最后一位
        $str = substr($str, 0, -1);

        return $str;
    }

    /**
     * 添加角色
     * @param $param
     * @return bool
     * @throws BasicException
     */
    public function roleAdd($param)
    {
        $res = app('model_sc_role')->create($param);
        if($res) return $res;
        throw new BasicException(90009);
    }


    /**
     * 调整公会收益比例
     * @param $param
     * @return bool
     * @throws BasicException
     */
    public function updateSociatyRate($param)
    {
        $sociaty = app('model_sc_sociaty')->where('sociaty_id',$param['sociaty_id'])->first();

        if(empty($sociaty)) throw new BasicException(30006);

        if(isset($param['room_rate'])) $sociaty->room_rate = $param['room_rate'];
        if(isset($param['user_rate'])) $sociaty->user_rate = $param['user_rate'];

        $res = $sociaty->save();
        if($res) return true;
        throw new BasicException(90014);
    }


    /**
     * 编辑公会备注
     * @param $param
     * @return bool
     * @throws BasicException
     */
    public function updateSociatyMemo($param)
    {
        $sociaty = app('model_sc_sociaty')->where('sociaty_id',$param['sociaty_id'])->first();

        if(empty($sociaty)) throw new BasicException(30006);

        $sociaty->memo = $param['memo'];

        $res = $sociaty->save();
        if($res) return true;
        throw new BasicException(90015);
    }


    /**
     * 公会列表
     * @param $param
     * @param $page
     * @param $perPage
     */
    public function sociatyList($param, $page, $perPage)
    {

        $list = app('model_sc_sociaty')->where('type',1);
        if(!empty($param['search'])) {
            //查询公会号,会长编号,公会名称
            $search = $param['search'];
            $list = $list->where(function ($query) use ($search) {
                $query->where('sociaty_no',$search)->orWhere('user_id',$search)->orWhere('sociaty_name','like',"%{$search}%");
            });
        }

        $list = $list->withOnly('user_data',['user_id', 'nickname','user_code'])
            ->select('user_id','sociaty_id','sociaty_name','user_rate','room_rate','memo','toplimit','sociaty_no')
            ->orderBy('sociaty_id','desc')->paginate($perPage, ['*'], '', $page);

        foreach($list as &$val)
        {
            $val->user_name = isset($val->user_data->nickname) ? $val->user_data->nickname : '';
            $val->user_code = isset($val->user_data->user_code) ? $val->user_data->user_code : '';
            unset($val->user_data);
        }

        return $list;
    }

    /**
     * 公会列表
     * @param $param
     * @param $page
     * @param $perPage
     */
    public function sociatyIndex($param, $page, $perPage)
    {

        //$list = app('model_sc_sociaty')->where('type',1);
        $list = app('model_sc_sociaty');
        if(!empty($param['search'])) {
            //查询公会号,会长编号,公会名称
            $search = $param['search'];
            $list = $list->where(function ($query) use ($search) {
                $query->where('sociaty_no',$search)->orWhere('user_id',$search)->orWhere('sociaty_name','like',"%{$search}%");
            });
        }
        if (isset($param['type'])) $list = $list->where('type',$param['type']);
        if (isset($param['status'])) $list = $list->where('status',$param['status']);

         $list = $list->withOnly('sociaty',['sociaty_id', 'sociaty_name as parent_name'])
            ->with('user_data:user_id,user_code')
            ->select('sociaty_id','sociaty_name','user_rate','room_rate','memo','toplimit','user_id','sociaty_no','type'
                ,'status','parent_id','admin_id','relations','avatar')
            ->orderBy('sociaty_id','desc')->paginate($perPage, ['*'], '', $page);

        foreach ($list as &$val) {
            $val->parent_name = isset($val->sociaty->parent_name) ? $val->sociaty->parent_name : '';
            $val->user_code = isset($val->user_data->user_code) ? $val->user_data->user_code : '';
            unset($val->sociaty);
            unset($val->user_data);
        }

        return $list;
    }

    /**
     * 公会编号编辑
     * @param $id
     * @param $param
     * @return mixed
     */
    public function sociatyNoEdit($param,$id)
    {
        $sociaty = app('model_sc_sociaty')->where('sociaty_id',$id)->where('type',$param['type'])->where('sociaty_no',$param['sociaty_no'])->first();
        if ($sociaty) {
            throw new BasicException(30020);
        }

        $res = app('model_sc_sociaty')->where('sociaty_id',$id)->update($param);

        if($res) return true;

        throw new BasicException(30018);
    }

    /**
     * 根据id获取公会详情
     * @param $id
     * @return mixed
     */
    public function sociatyDetailById($id,$type='')
    {
        $sociaty = app('model_sc_sociaty')->where('sociaty_id',$id);
        if(!empty($type)) $sociaty = $sociaty->where('type',$type);
        $sociaty = $sociaty->first();

        return $sociaty;
    }

    /**
     * 编辑直属厅最高上限
     * @param $sociaty_id
     * @param $limit
     * @return bool
     * @throws BasicException
     */
    public function updateTopLimit($sociaty_id,$limit)
    {
        $sociaty = $this->sociatyDetailById($sociaty_id,1);

        if(empty($sociaty)) throw new BasicException(30006);

        if(!empty($limit)) $sociaty->toplimit = $limit;

        $res = $sociaty->save();

        if($res) return true;

        throw new BasicException(90029);
    }

    /**
     * 更新公会表ByadminId
     * @param $admin_id
     * @param $sc_id
     * @return bool
     * @throws BasicException
     */
    public function updateAdminId($admin_id,$sc_id)
    {
        $sociaty = $this->sociatyDetailById($sc_id);
        if(empty($sociaty)) throw new BasicException(30006);

        $sociaty->admin_id = $admin_id;
        $res = $sociaty->save();
        if($res) return true;

        throw new BasicException(30007);
    }

    /**
     * 解散工会
     * @param
     * @return bool
     * @throws BasicException
     */
    public function dismissSociaty($sociaty_id)
    {
        $sociaty = app('model_sc_sociaty')->where('sociaty_id', $sociaty_id)->first();

        if(empty($sociaty)) throw new BasicException(30006);

        //查询下边所有的厅
        //$hall = app('model_sc_sociaty')->where('parent_id',$sociaty_id)->where('type', 2)->get()->toArray();
        $hall = app('model_sc_sociaty')->where('parent_id',$sociaty_id)->where('type', 2)->get();
        //if(empty($hall)) throw new BasicException(30015);
        $sociaty_no = 0;
        $user_no = 0;

        // 后期优化
        foreach($hall as &$val) {
            //清退该厅-记录日志
            $hall_id = $val->sociaty_id;
            $hall_detail = app('model_sc_sociaty')->where('sociaty_id',$hall_id)->first();
            $hall_detail->parent_id = 0;
            $hall_detail->relations = 0;
            $hall_detail->save();

            $hall_record = [
                'sociaty_id' => $sociaty_id,
                'hall_id' => $hall_id,
                'add_time' => time(),
                'type' => 2
                // relations
            ];

            app('model_sc_sociaty_history')->create($hall_record);
            $user_list = app('model_sc_sociaty_user')->where('sociaty_id', $hall_id)->get();

            //删除绑定关系
            foreach($user_list as &$nal) {
                app('model_sc_sociaty_user')->where('id', $nal->id)->delete();

                //记录日志
                $user_record = [
                    'sociaty_id' => $nal->sociaty_id,
                    'user_id' => $nal->user_id,
                    'add_time' => time(),
                    'type' => 2
                ];

                app('model_sc_user_history')->create($user_record);

                $user_no += 1;
            }
            $sociaty_no += 1;
        }
        return '共解绑'. $sociaty_no . '个直属厅,解绑'. $user_no . '个用户';
    }

    /**
     * 收益结算列表
     * @param $type
     * @param $param
     * @param $page
     * @param $perPage
     * @return mixed
     */
    public function statisticScList($type, $param, $page, $perPage)
    {
        $time = CommonService::transTime($param['start_at'],$param['end_at']);

        $list = app('model_sc_statistic')->whereBetween('count_time',[$time['start_at'],$time['end_at']])->where('type',$type);

        if(!empty($param['status'])) $list = $list->where('status',$param['status']);

        if(!empty($param['search'])) {
            $search = $param['search'];

            //查询公会号,会长编号
            $list = $list->where(function ($query) use ($search){
                $query->whereHas('user_data',function ($query) use ($search){
                    $query->from(env('DB_DATABASE') . '.ln_user_data')->where('user_code',$search);
                })->orWhereHas('sociaty',function ($query) use ($search){
                    $query->where('sociaty_no',$search);
                });
            });
        } else {
            $list = $list->withOnly('sociaty',['sociaty_id','sociaty_name','sociaty_no'])->with('user_data:user_id,user_code');
        }

        $list = $list
            //->withOnly('sociaty',['sociaty_id','sociaty_name','sociaty_no'])
            //->with('user_data:user_id,user_code')
            ->with('u_data:user_id,user_code')
            ->select('id','sociaty_id','user_id','count_time','amount','status','memo','uid','real_amount','rate','reversal','should_amount')
            ->orderBy('id','desc')->paginate($perPage, ['*'], '', $page);

        foreach ($list as &$val) {
            $val->sociaty_name = isset($val->sociaty->sociaty_name) ? $val->sociaty->sociaty_name : '';
            $val->sociaty_no = isset($val->sociaty->sociaty_no) ? $val->sociaty->sociaty_no : '';
            $val->user_code = isset($val->user_data->user_code) ? $val->user_data->user_code : '';
            $val->u_code = isset($val->u_data->user_code) ? $val->u_data->user_code : '';
            unset($val->sociaty);
            unset($val->user_data);
            unset($val->u_data);
        }

        return $list;

    }


    /**
     * 编辑结算单备注
     * @param $param
     * @return bool
     * @throws BasicException
     */
    public function statisticUpdateMemo($param)
    {
        $statistic = app('model_sc_statistic')->where('id',$param['id'])->first();

        if(empty($statistic)) throw new BasicException(30007);

        $statistic->memo = $param['memo'];

        $res = $statistic->save();
        if($res) return true;
        throw new BasicException(90016);
    }


    /**
     * 获取可以冻结的所有id数量
     * @param $id_arr
     * @return mixed
     */
    public function countStatisticByIds($id_arr)
    {
        $count = app('model_sc_statistic')->whereIn('id',$id_arr)->where('status',1)->count();

        return $count;
    }


    /**
     * 冻结结算单
     * @param $id_arr
     * @return bool
     * @throws BasicException
     */
    public function statisticFreeze($id_arr)
    {
        $res = app('model_sc_statistic')->whereIn('id',$id_arr)->update(['status'=>3]);

        if($res) return true;

        throw new BasicException(90017);
    }


    /**
     * 解冻结算单(单个)
     * @param $id
     * @return bool
     * @throws BasicException
     */
    public function statisticUnfreeze($id)
    {
        $statistic = app('model_sc_statistic')->where(['id'=>$id,'status'=>3])->first();

        if(empty($statistic)) throw new BasicException(30010);

        $statistic->status = 1;
        $res = $statistic->save();
        if($res) return true;
        throw new BasicException(90018);
    }


    /**
     * 编辑结算单
     * @param $param
     * @return bool
     * @throws BasicException
     */
    public function statisticUpdate($param)
    {
        $statistic = app('model_sc_statistic')->where(['id'=>$param['id'],'status'=>1])->first();

        if(empty($statistic)) throw new BasicException(30011);

        if(!empty($param['uid'])) {
            //检查是否有该uid
            $user = $this->searchUser($param['uid']);
            if(!$user) throw new BasicException(30012);
            $statistic->uid = $user;
        }

        if(isset($param['rate'])) $statistic->real_rate = $param['rate'];

        $res = $statistic->save();
        if($res) return true;
        throw new BasicException(90019);
    }


    /**
     * 实时查找用户
     * @param $uid
     * @return bool
     */
    public function searchUser($uid)
    {
        $user = app('model_user_data')->where('user_code',$uid)->first();

        if($user) return $user->user_id;

        return false;
    }


    /**
     * 发放奖励
     * @param $id
     * @param $admin_id
     * @throws BasicException
     */
    public function statisticAgree($id,$admin_id)
    {
        //开启事务去执行发放奖励操作
        $statistic = app('model_sc_statistic')->where('id',$id)->first();
        $user = app('model_user')->where('user_id',$statistic->uid)->where('deleted', 0)->first();
        $money = ($statistic->amount) * ($statistic->real_rate) / 100;
        //财务日志变动数据
        $log_data = [
            'user_id' => $statistic->uid,
            'change_time' => time(),
            'user_money' => $money,
            'order_sn' => $id,
            'change_desc' => '发放公会流水提成比例',
            'change_type' => 35 //新增一个类别10,公会流水赠送
        ];

        //开启事务执行创建申请记录的操作---跨库事务操作
        DB::beginTransaction();
        DB::connection('mysql_sociaty')->beginTransaction();
        try{
            //编辑原表
            $statistic->admin_id = $admin_id;
            $statistic->update_time = time();
            $statistic->status = 2;
            $statistic->real_amount = $money;
            $statistic->save();

            //变动用户的金额
            if(!empty($user)) {

                $user->earnings = !empty($user->earnings) ? ($user->earnings + $money) : $money;
                $user->save();
            }

            //记录日志
            app('model_account_log')->create($log_data);

            DB::commit();
            DB::connection('mysql_sociaty')->commit();

        }catch (\Exception $e) {

            DB::rollBack();
            DB::connection('mysql_sociaty')->rollBack();

            throw new BasicException(90020);
        }

        return true;

    }


    /**
     * 比例配置列表
     * @param $type
     * @return mixed
     */
    public function incomeRateConfList($type)
    {
        $data = app('model_sc_income_rate_conf')->where('type',$type)->with('admin')->get();
        foreach ($data as $val) {
            $val->admin_account = isset($val->admin) ? $val->admin->admin_name : '';
            unset($val->admin);
        }
        return $data;
    }

    /**
     * 比例配置添加
     * @param $param
     * @return mixed
     */
    public function incomeRateConfAdd($param)
    {
        $data = app('model_sc_income_rate_conf')->create($param);
        if($data) return true;

        throw new BasicException(30016);
    }

    /**
     * 比例配置编辑
     * @param $id
     * @param $param
     * @return mixed
     */
    public function updateRate($param,$id)
    {
        $res = app('model_sc_income_rate_conf')->where('id',$id)->update($param);

        if($res) return true;

        throw new BasicException(30018);
    }

    /**
     * 比例配置删除
     * @param $id
     * @return mixed
     */
    public function incomeRateConfDelete($id)
    {
        $data = app('model_sc_income_rate_conf')->where('id',$id)->delete();
        if($data) return true;

        throw new BasicException(30017);
    }

    /**
     * 厅列表
     * @param $param
     * @param $page
     * @param $perPage
     * @return mixed
     */
    public function hallIndex($param, $page, $perPage)
    {

/*        if (isset($param['start_at']) && isset($param['end_at'])) {
            $time = CommonService::transTime($param['start_at'],$param['end_at']);
            $list = app('model_sc_statistic')->where('genre',1)->where('pid',$param['sociaty_id'])
                ->whereBetween('count_time',[$time['start_at'],$time['end_at']]);
        } else {
            $list = app('model_sc_statistic')->where('genre',1)->where('pid',$param['sociaty_id']);
        }*/

        $list = app('model_sc_statistic')->where('genre',1)->where('pid',$param['sociaty_id'])->where('count_time',$param['count_time']);

        if(!empty($param['status'])) $list = $list->where('status',$param['status']);

        if(!empty($param['search'])) {
            //查询公会号,会长编号,公会名称
            $search = $param['search'];
            $list = $list->where(function ($query) use ($search) {
                $query->where('sociaty_id',$search)->orWhere('user_id',$search);
            });
        }

        $sys_type = app('model_room_type')->where('type',1)->pluck('room_type_name','id')->toArray();

        //$list = $list->withOnly('sociaty',['sociaty_id','sociaty_name','room_id'])
        $list = $list->with('sociaty:sociaty_id,sociaty_name,room_id')
            ->with('user_data:user_id,user_code,nickname')
            ->with('u_data:user_id,user_code,nickname')
            ->with('user_room:room_id,user_id,room_name,sys_type_id')
            ->select('id','sociaty_id','user_id','count_time','amount','status','memo','uid','real_amount','rate','real_rate','pid','reversal')
            ->orderBy('id','desc')->paginate($perPage, ['*'], '', $page);
        foreach ($list as &$val) {

            $room = app('model_user_room')->where('id',$val->sociaty->room_id)->with('user_data:user_id,user_code,nickname')->first();

            $val->sociaty_name = isset($val->sociaty->sociaty_name) ? $val->sociaty->sociaty_name : '';
            //$val->user_code = isset($val->user_data->user_code) ? $val->user_data->user_code : '';
            $val->nickname = isset($room->user_data->nickname) ? $room->user_data->nickname : '';
            $val->u_code = isset($val->u_data->user_code) ? $val->u_data->user_code : '';
            $val->room_name = isset($room->room_name) ? $room->room_name : '';
            $val->room_code = isset($room->room_id) ? $room->room_id : '';
            //$val->room_type_name = isset($sys_type[$val->user_room->sys_type_id]) ? $sys_type[$val->user_room->sys_type_id] : '';
            if (!isset($room->sys_type_id) || $room->sys_type_id == 0) {
                $val->room_type_name = '';
            } else {
                $val->room_type_name = $sys_type[$room->sys_type_id]??'';
            }
            $val->room_user_id = isset($room->user_data->user_code) ? $room->user_data->user_code : '';
            unset($val->sociaty);
            unset($val->user_data);
            unset($val->u_data);
            unset($val->pid);
        }

        return $list;
    }

    /**
     * 厅收益结算列表
     * @param $param
     * @param $page
     * @param $perPage
     * @return mixed
     */
    public function statisticHallList($param, $page, $perPage)
    {
        if (isset($param['start_at']) && isset($param['end_at'])) {
            $time = CommonService::transTime($param['start_at'],$param['end_at']);
            $list = app('model_sc_statistic')->where('genre',1)->where('pid',$param['sociaty_id'])
                ->whereBetween('count_time',[$time['start_at'],$time['end_at']]);
        } else {
            $list = app('model_sc_statistic')->where('genre',1)->where('pid',$param['sociaty_id']);
        }

        if(!empty($param['status'])) $list = $list->where('status',$param['status']);

        if(!empty($param['search'])) {
            //查询公会号,会长编号,公会名称
            $search = $param['search'];
            $list = $list->where(function ($query) use ($search) {
                $query->where('sociaty_id',$search)->orWhere('user_id',$search);
            });
        }

        $list = $list->withOnly('sociaty',['sociaty_id','sociaty_name'])->with('user_data:user_id,user_code')->with('u_data:user_id,user_code')
            ->select('id','sociaty_id','user_id','count_time','amount','status','memo','uid','real_amount','rate','reversal')
            ->orderBy('id','desc')->paginate($perPage, ['*'], '', $page);

        foreach($list as &$val)
        {
            $val->sociaty_name = isset($val->sociaty->sociaty_name) ? $val->sociaty->sociaty_name : '';
            $val->user_code = isset($val->user_data->user_code) ? $val->user_data->user_code : '';
            $val->u_code = isset($val->u_data->user_code) ? $val->u_data->user_code : '';
            unset($val->sociaty);
            unset($val->user_data);
            unset($val->u_data);
        }

        return $list;

    }

    /**
     * 订单列表
     * @param $param
     * @param $page
     * @param $perPage
     * @return mixed
     */
    public function orderIndex($param, $page, $perPage)
    {
        if (isset($param['start_at']) && isset($param['end_at'])) {
            $time = CommonService::transTime($param['start_at'],$param['end_at']);
            $list = app('model_sc_statistic')->whereBetween('count_time',[$time['start_at'],$time['end_at']])->where('type',4);
        } else {
            $list = app('model_sc_statistic')->where('type',4);
        }

        if(!empty($param['status'])) $list = $list->where('status',$param['status']);

        if(!empty($param['search'])) {
            $search = $param['search'];

            //查询公会号,会长编号
            $list = $list->where(function ($query) use ($search){
                $query->whereHas('user_data',function ($query) use ($search){
                    $query->from(env('DB_DATABASE') . '.ln_user_data')->where('user_code',$search);
                })->orWhereHas('sociaty',function ($query) use ($search){
                    $query->where('sociaty_no',$search);
                });
            });
        } else {
            $list = $list->withOnly('sociaty',['sociaty_id','sociaty_name','sociaty_no'])->with('user_data:user_id,user_code,nickname');
        }

        $list = $list
            //->withOnly('sociaty',['sociaty_id','sociaty_name','sociaty_no'])
            //->with('user_data:user_id,user_code,nickname')
            ->with('u_data:user_id,user_code,nickname')
            ->select('id','sociaty_id','user_id','count_time','amount','status','memo','uid','real_amount','rate','real_rate','reversal')
            ->orderBy('id','desc')->paginate($perPage, ['*'], '', $page);

        foreach($list as &$val)
        {
            $val->sociaty_name = isset($val->sociaty->sociaty_name) ? $val->sociaty->sociaty_name : '';
            $val->sociaty_no = isset($val->sociaty->sociaty_no) ? $val->sociaty->sociaty_no : '';
            $val->user_code = isset($val->user_data->user_code) ? $val->user_data->user_code : '';
            $val->nickname = isset($val->user_data->nickname) ? $val->user_data->nickname : '';
            $val->u_code = isset($val->u_data->user_code) ? $val->u_data->user_code : '';
            unset($val->sociaty);
            unset($val->user_data);
            unset($val->u_data);
        }

        return $list;
    }

    /**
     * 厅订单列表
     * @param $param
     * @param $page
     * @param $perPage
     * @return mixed
     */
    public function hallOrderIndex($param, $page, $perPage)
    {
        if (isset($param['start_at']) && isset($param['end_at'])) {
            $time = CommonService::transTime($param['start_at'],$param['end_at']);
            $list = app('model_sc_statistic')->where('genre',2)->where('pid',$param['sociaty_id'])
                ->whereBetween('count_time',[$time['start_at'],$time['end_at']]);
        } else {
            $list = app('model_sc_statistic')->where('genre',2)->where('pid',$param['sociaty_id']);
        }

        if(!empty($param['status'])) $list = $list->where('status',$param['status']);

        if(!empty($param['search'])) {
            //查询公会号,会长编号,公会名称
            $search = $param['search'];
            $list = $list->where(function ($query) use ($search) {
                $query->where('sociaty_id',$search)->orWhere('user_id',$search);
            });
        }

        $list = $list->withOnly('sociaty',['sociaty_id','sociaty_name'])->with('user_data:user_id,user_code,nickname')->with('u_data:user_id,user_code')
            ->select('id','sociaty_id','user_id','count_time','amount','status','memo','uid','real_amount','rate','real_rate','reversal','pid')
            ->orderBy('id','desc')->paginate($perPage, ['*'], '', $page);

        foreach($list as &$val)
        {
            $val->sociaty_name = isset($val->sociaty->sociaty_name) ? $val->sociaty->sociaty_name : '';
            $val->user_code = isset($val->user_data->user_code) ? $val->user_data->user_code : '';
            $val->nickname = isset($val->user_data->nickname) ? $val->user_data->nickname : '';
            $val->u_code = isset($val->u_data->user_code) ? $val->u_data->user_code : '';
            unset($val->sociaty);
            unset($val->user_data);
            unset($val->u_data);
        }

        return $list;
    }

    /**
     * 编辑冲正
     * @param $param
     * @return bool
     * @throws BasicException
     */
    public function updateOrderReversal($param)
    {
        $statistic = app('model_sc_statistic')->where(['id'=>$param['id'],'status'=>1])->first();

        if(empty($statistic)) throw new BasicException(30011);

        if(!empty($param['uid'])) {
            //检查是否有该uid
            $user = $this->searchUser($param['uid']);
            if(!$user) throw new BasicException(30012);
            $statistic->uid = $user;
        }

        if(isset($param['reversal'])) $statistic->reversal = $param['reversal'];

        $res = $statistic->save();
        if($res) return true;
        throw new BasicException(90019);
    }

    /**
     * 公会列表编辑冲正更新公会实发金额
     * @param $param
     * @return bool
     * @throws BasicException
     */
    public function updateSociatyReversal($param)
    {
        $statistic = app('model_sc_statistic')->where(['id'=>$param['id'],'status'=>1])->first();

        if(empty($statistic)) throw new BasicException(30011);

        if(!empty($param['uid'])) {
            //检查是否有该uid
            $user = $this->searchUser($param['uid']);
            if(!$user) throw new BasicException(30012);
            $statistic->uid = $user;
        }

        if(isset($param['reversal'])) {
            $statistic->reversal = $param['reversal'];
/*            $num = ($statistic->should_amount) + ($statistic->reversal);
            $statistic->should_amount = sprintf("%.2f",$num);*/
        }

        $res = $statistic->save();
        if($res) return true;

        throw new BasicException(90019);
    }


    /**
     * 编辑冲正更新公会实发金额
     * @param $param
     * @return bool
     * @throws BasicException
     */
    public function updateReversalChangeShouldAmount($param)
    {
        $statistic = app('model_sc_statistic')->where(['id'=>$param['id'],'status'=>1])->first();

        if(empty($statistic)) throw new BasicException(30011);

        if(!empty($param['uid'])) {
            //检查是否有该uid
            $user = $this->searchUser($param['uid']);
            if(!$user) throw new BasicException(30012);
            $statistic->uid = $user;
        }

        if(isset($param['reversal'])) {
            $statistic->reversal = $param['reversal'];
/*            $num = ($statistic->amount * $statistic->rate / 100) + ($statistic->reversal);
            $statistic->should_amount = sprintf("%.2f",$num);*/
        }

        $res = $statistic->save();
        if($res) {
/*            if($param['reversal'] != 0) {

                $num = app('model_sc_statistic')
                    ->where(['status'=>1,'genre'=>1,'pid'=>$statistic->pid,'count_time'=>$statistic->count_time])
                    ->groupBy('pid')
                    ->select(DB::raw("sum(should_amount) as should_amount"),'pid')
                    ->first();
                $should_amount = sprintf("%.2f",$num->should_amount);

                $sociaty = app('model_sc_statistic')
                    ->where(['type'=>1,'sociaty_id'=>$statistic->pid,'count_time'=>$statistic->count_time])->first();
                if ($sociaty->should_amount != $should_amount) {
                    app('model_sc_statistic')->where(['type'=>1,'sociaty_id'=>$statistic->pid,'count_time'=>$statistic->count_time])
                        ->update(['should_amount'=>$should_amount]);
                }
            }*/
            return true;
        }
        throw new BasicException(90019);
    }

    /**
     * 特殊房间流水列表
     * @param $param
     * @param $page
     * @param $perPage
     * @return mixed
     */
    public function statisticRoomList($param, $page, $perPage)
    {
        if (isset($param['start_at']) && isset($param['end_at'])) {
            $time = CommonService::transTime($param['start_at'],$param['end_at']);
            $list = app('model_sc_statistic')->where('type',3)
                ->whereBetween('count_time',[$time['start_at'],$time['end_at']]);
        } else {
            $list = app('model_sc_statistic')->where('type',3);
        }

        if(!empty($param['status'])) $list = $list->where('status',$param['status']);

        if(!empty($param['search'])) {
            $search = $param['search'];

            //房间ID,查询房主ID
            $list = $list->where(function ($query) use ($search){
                $query->whereHas('user_room',function ($query) use ($search){
                    $query->from(env('DB_DATABASE') . '.ln_user_room')->where('room_id',$search);
                })->orWhereHas('user_data',function ($query) use ($search){
                    $query->from(env('DB_DATABASE') . '.ln_user_data')->where('user_code',$search);
                });
            });
        } else {
            $list = $list->with('user_data:user_id,user_code,nickname');
        }

        //查询系统房间类型 特殊房间流水 type=1 系统分类
        $sys_type = app('model_room_type')->where('type',1)->pluck('room_type_name','id')->toArray();

        $list = $list->withOnly('sociaty',['sociaty_id','sociaty_name'])
            //->with('user_room:room_id,user_id,room_name,sys_type_id')
            //->with('user_data:user_id,user_code,nickname')
            ->with('u_data:user_id,user_code')
            ->select('id','sociaty_id','user_id','count_time','amount','status','memo','uid','real_amount','rate','real_rate','reversal','pid')
            ->orderBy('id','desc')->paginate($perPage, ['*'], '', $page);

        foreach ($list as &$val) {
            $room = app('model_user_room')->where('id',$val->pid)->first();
            $val->user_code = isset($val->user_data->user_code) ? $val->user_data->user_code : '';
            $val->nickname = isset($val->user_data->nickname) ? $val->user_data->nickname : '';
            $val->u_code = isset($val->u_data->user_code) ? $val->u_data->user_code : '';
            $val->room_name = isset($room->room_name) ? $room->room_name : '';
            $val->room_code = isset($room->room_id) ? $room->room_id : '';

            if (!isset($room->sys_type_id) || $room->sys_type_id == 0) {
                $val->room_type_name = '';
            } else {
                $val->room_type_name = $sys_type[$room->sys_type_id]??'';
            }

            //$val->room_type_name = $sys_type[$room->sys_type_id];
            unset($val->sociaty);
            unset($val->user_data);
            unset($val->u_data);
            unset($val->user_room);
        }

        return $list;
    }

    /**
     * 发放奖励
     * @param $id
     * @param $admin_id
     * @throws BasicException
     */
    public function statisticAgrees($id,$admin_id)
    {
        //开启事务去执行发放奖励操作
        $statistic = app('model_sc_statistic')->where('id',$id)->first();
        $user = app('model_user')->where('user_id',$statistic->uid)->where('deleted', 0)->first();
        $money = ($statistic->amount) * ($statistic->rate) / 100 + ($statistic->reversal);
        //财务日志变动数据
        $log_data = [
            'user_id' => $statistic->uid,
            'change_time' => time(),
            'user_money' => $money,
            'order_sn' => $id,
            'change_desc' => '发放公会流水提成比例',
            'change_type' => 35 //新增一个类别10,公会流水赠送
        ];

        //开启事务执行创建申请记录的操作---跨库事务操作
        DB::beginTransaction();
        DB::connection('mysql_sociaty')->beginTransaction();
        try{
            //编辑原表
            $statistic->admin_id = $admin_id;
            $statistic->update_time = time();
            $statistic->status = 2;
            $statistic->real_amount = $money;
            $statistic->save();

            //变动用户的金额
            if(!empty($user)) {

                $user->earnings = !empty($user->earnings) ? ($user->earnings + $money) : $money;
                $user->save();
            }

            //记录日志
            app('model_account_log')->create($log_data);

            DB::commit();
            DB::connection('mysql_sociaty')->commit();

        }catch (\Exception $e) {

            DB::rollBack();
            DB::connection('mysql_sociaty')->rollBack();

            throw new BasicException(90020);
        }

        return true;

    }


    /**
     * 发放奖励
     * @param $id
     * @param $admin_id
     * @throws BasicException
     */
    public function sociatyStatisticAgrees($id,$admin_id)
    {
        //开启事务去执行发放奖励操作
        $statistic = app('model_sc_statistic')->where('id',$id)->first();
        $user = app('model_user')->where('user_id',$statistic->uid)->where('deleted', 0)->first();
        $money = ($statistic->should_amount) + ($statistic->reversal);
        //财务日志变动数据
        $log_data = [
            'user_id' => $statistic->uid,
            'change_time' => time(),
            'user_money' => $money,
            'order_sn' => $id,
            'change_desc' => '发放公会流水提成比例',
            'change_type' => 35 //新增一个类别10,公会流水赠送
        ];

        //开启事务执行创建申请记录的操作---跨库事务操作
        DB::beginTransaction();
        DB::connection('mysql_sociaty')->beginTransaction();
        try{
            //编辑原表
            $statistic->admin_id = $admin_id;
            $statistic->update_time = time();
            $statistic->status = 2;
            $statistic->real_amount = $money;
            $statistic->save();

            //变动用户的金额
            if(!empty($user)) {

                $user->earnings = !empty($user->earnings) ? ($user->earnings + $money) : $money;
                $user->save();
            }

            //记录日志
            app('model_account_log')->create($log_data);

            DB::commit();
            DB::connection('mysql_sociaty')->commit();

        }catch (\Exception $e) {

            DB::rollBack();
            DB::connection('mysql_sociaty')->rollBack();

            throw new BasicException(90020);
        }

        return true;

    }

    /**
     * 获取特殊房间列表
     * @param $param
     * @param $page
     * @param $perPage
     * @return mixed
     */
    public function getSpecialRoom($param, $page, $perPage)
    {
        $list = app('model_sc_special_room');

        $list = $list->with('user_room:id,room_id,sys_type_id')->with('user_data:user_id,user_code');
        if (!empty($param['search'])) {
            $search = $param['search'];

            $list = $list->whereHas('user_room',function($query) use ($search){
                $query->from(env('DB_DATABASE') . '.ln_user_room')->where('room_id',$search);
            })->orWhereHas('user_data',function($query) use ($search){
                $query->from(env('DB_DATABASE') . '.ln_user_data')->where('user_code',$search);
            });
        }

        if (!empty($param['type_id'])) {
            $type_id = $param['type_id'];
            $list = $list->whereHas('user_room',function($query) use ($type_id){
                $query->from(env('DB_DATABASE') . '.ln_user_room')->where('sys_type_id',$type_id);
            });
        }

        $list = $list->with('sociaty:sociaty_name,sociaty_no,room_id,parent_id');

        $list = $list->orderBy('id','desc')->paginate($perPage, ['*'], '', $page);

        foreach ($list as &$val) {
            $val->room_id = $val->user_room->room_id;
            $val->user_code = $val->user_data->user_code;

            if(isset($val->sociaty->parent_id)) {
                $sociaty = app('model_sc_sociaty')->where('sociaty_id',$val->sociaty->parent_id)->select('sociaty_no','sociaty_name')->first();
            }

            if(isset($val->user_room->sys_type_id)) {
                $type_name = app('model_room_type')->where('id',$val->user_room->sys_type_id)->pluck('room_type_name')->first();
            }

            $val->type_name = isset($type_name) ? $type_name : "";
            $val->sociaty_no = isset($sociaty->sociaty_no) ? $sociaty->sociaty_no : "";
            $val->sociaty_name = isset($sociaty->sociaty_name) ? $sociaty->sociaty_name : "";
            $val->rate = $this->getRateList($val->id);

            unset($val->user_room);
            unset($val->user_data);
            unset($val->sociaty);
        }

        return $list;
    }

    /**
     * 获取所有比例
     * @param $id
     * @return mixed
     */
    public function getRateList($id)
    {
        $list = app('model_sc_special_room_rate')->where('special_room_id',$id)->orderBy('rate')->select('rate','coin_min','coin_max')->get();
        return $list;
    }

    /**
     * 获取特殊房间分类
     * @return mixed
     */
    public function getRoomType()
    {
        $list = app('model_room_type')->where('type',1)->orderBy('sort','desc')->select('id','room_type_name')->get();
        return $list;
    }

    /**
     * 获取房间名称
     * @param $room_id
     * @return mixed
     */
    public function getRoomName($room_id)
    {
        $list = app('model_user_room')->where('room_id',$room_id)->pluck('room_name')->first();
        if(empty($list)) {
            throw new BasicException(30028);
        }
        return $list;
    }

    /**
     * 添加特殊房间
     * @param $room_id
     * @param $param
     * @return mixed
     */
    public function specialRoomAdd($room_id,$param)
    {
        $room = app('model_user_room')->where('room_id',$room_id)->select('id','room_name','user_id')->first();
        $time = time();
        $result = app('model_sc_special_room')->where('room_id',$room->id)->exists();
        if ($result) {
            throw new BasicException(30023);
        }

        $res = app('model_sc_special_room')->create(['room_id'=>$room->id,'user_id'=>$room->user_id,'add_time'=>$time]);

        if ($res) {

        foreach ($param as $data) {
            $data['special_room_id'] = $res->id;
            $data['room_id'] = $room->id;
            $data['add_time'] = $time;

            app('model_sc_special_room_rate')->create($data);
        }

/*            $no = 0;
            foreach ($param as $val) {

                $data = [
                    'coin_min' => $param['coin_min'][$no],
                    'coin_max' => $param['coin_max'][$no],
                    'rate' => $param['rate'][$no],
                    'special_room_id' => $res->id,
                    'room_id' => $room->id,
                    'add_time' => $time,
                ];
                app('model_sc_special_room_rate')->create($data);

                if ($no >= count($val)-1) {
                    return true;
                }

                $no += 1;
            }*/
            return true;
        }
        throw new BasicException(30024);
    }

    /**
     * 编辑特殊房间
     * @param $special_room_id
     * @param $param
     * @return mixed
     */
    public function specialRoomEdit($special_room_id, $param)
    {
        $room = app('model_sc_special_room')->where('id',$special_room_id)->first();
        if (!$room) {
            throw new BasicException(30026);
        }
        $res = app('model_sc_special_room_rate')->where('special_room_id',$special_room_id)->delete();
        if ($res) {
            $time = time();

            foreach ($param as $data) {
                $data['special_room_id'] = $special_room_id;
                $data['room_id'] = $room->room_id;
                $data['add_time'] = $time;

                app('model_sc_special_room_rate')->create($data);
            }


/*            $no = 0;
            foreach ($param as $val) {

                $data = [
                    'coin_min' => $param['coin_min'][$no],
                    'coin_max' => $param['coin_max'][$no],
                    'rate' => $param['rate'][$no],
                    'special_room_id' => $special_room_id,
                    'room_id' => $room->room_id,
                    'add_time' => $time,
                ];
                app('model_sc_special_room_rate')->create($data);

                if ($no >= count($val)-1) {
                    return true;
                }

                $no += 1;
            }*/
            return true;
        }
        throw new BasicException(30025);
    }

    /**
     * 删除特殊房间信息
     * @param $arr_ids
     * @return mixed
     */
    public function specialRoomDelete($arr_ids)
    {
        $data = app('model_sc_special_room')->whereIn('id',$arr_ids)->delete();
        if($data) {
            app('model_sc_special_room_rate')->whereIn('special_room_id',$arr_ids)->delete();
            return true;
        }

        throw new BasicException(30027);
    }


    /**
     * 运营看公会流水数据
     * @param $param
     * @param $page
     * @param $perPage
     * @return mixed
     */
    public function getOperationScData($param, $page, $perPage)
    {

        $time = CommonService::transStaticWeekTime($param['start_at'],$param['end_at']);

        $list = app('model_sc_sociaty')->where('type',1);

        if(!empty($param['sociaty_no'])) $list = $list->where('sociaty_no',$param['sociaty_no']);

        if(!empty($param['search'])) $list = $list->where('sociaty_name','like',"%{$param['search']}%");

        $list = $list->select('sociaty_id','sociaty_no','sociaty_name','add_time','avatar','user_id')

            ->withCount(['income_room as room_total'=>function($query) use ($time){

                $query->whereBetween('add_time',[$time['start_at'],$time['end_at']])->select(DB::raw('sum(amount) as total'),'sc_id');

            }])->withCount(['income_user as room_user_total'=>function($query) use ($time){

                $query->whereBetween('add_time',[$time['start_at'],$time['end_at']])->where('type',1)->select(DB::raw('sum(amount) as total'),'sc_id');

            }])->paginate($perPage, ['*'], '', $page);

        foreach($list as &$val)
        {
            $val->user_code = isset($val->user_data->user_code) ? $val->user_data->user_code : 0;
            $val->nickname = isset($val->user_data->nickname) ? $val->user_data->nickname : '';
            $val->income_room_data = !empty($val->room_total) ? $val->room_total : 0;
            $val->income_user_data = !empty($val->room_user_total) ? $val->room_user_total : 0;
            $val->add_time = date('Y-m-d H:i:s',$val->add_time);
            unset($val->room_user_total);
            unset($val->room_total);
            unset($val->user_data);
            unset($val->user_id);

        }

        return $list;
    }


}
